package com.sduept.nwld.dataserver.util;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExicelParseUtil {

	private FileInputStream fis;
	private HSSFWorkbook wb;
	private HSSFSheet sheet;
	private HSSFRow row;
	private HSSFCell cell;
	private XSSFWorkbook xssfWorkbook;
	private XSSFSheet xssfSheet;
	private XSSFRow xssfRow;
	private XSSFCell cellValue;

	/**
	 * xls 返回一个sheet内容
	 * 
	 * @param path
	 * @param startRowNum
	 *            开始解析的行号
	 * @param sheetNum
	 *            sheet的页数
	 * @return
	 */
	public List<List<Object>> getFirstRowInfoOfXLS(String path,
			int startRowNum, int sheetNum) {
		boolean isMerge = false;
		List<Object> _cellValues = new ArrayList<Object>();
		List<Object> _tempCellValues = new ArrayList<Object>();
		List<List<Object>> cells = new ArrayList<List<Object>>();
		try {
			fis = new FileInputStream(path);
			wb = new HSSFWorkbook(fis);
			int num = wb.getNumberOfSheets();
			if (sheetNum >= 0 && sheetNum <= num) {
				sheet = wb.getSheetAt(sheetNum);
			} else {
				return null;
			}

			if (sheet != null) {
				while (true) {
					row = sheet.getRow(startRowNum);
					if (row != null) {
						for (Cell c : row) {
							isMerge = isMergedRegion(sheet, startRowNum,
									c.getColumnIndex());
							if (isMerge)
								break;
						}
						if (isMerge) {
							for (Cell _c : row) {
								boolean _isMerge = isMergedRegion(sheet,
										startRowNum, _c.getColumnIndex());
								// 判断是否具有合并单元格
								if (_isMerge) {
									String rs = getMergedRegionValue(sheet,
											startRowNum, _c.getColumnIndex());
									_cellValues.add(rs);
								} else {
									_cellValues.add(getCellValue(_c));
								}
							}
							for (Object _cellValue : _cellValues) {
								if (!_tempCellValues.contains(_cellValue)) {
									_tempCellValues.add(_cellValue);
								}
							}
							List<Object> objs = new ArrayList<>();
							for (Object _tempCellValue : _tempCellValues) {
								objs.add(_tempCellValue);
							}
							cells.add(objs);
						} else {
							List<Object> objs = new ArrayList<>();
							for (int cellNum = 0; cellNum < row
									.getLastCellNum(); cellNum++) {
								cell = row.getCell(cellNum);
								if (cell != null) {
									objs.add(cell.toString());
								} else {
									objs.add("");
								}

							}
							cells.add(objs);
						}
						startRowNum++;
					} else {
						break;
					}

				}
			}
			fis.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cells;
	}

	/**
	 * xlsx 返回一个sheet内容
	 * 
	 * @param path
	 * @param startRowNum
	 *            开始解析的行号
	 * @return
	 */
	public List<List<Object>> getFirstRowInfoOfXLSX(String path,
			int startRowNum, int sheetNum) {
		List<List<Object>> cellValues = new ArrayList<List<Object>>();
		boolean isMerge = false;
		List<Object> _cellValues = new ArrayList<>();
		List<Object> _tempCellValues = new ArrayList<>();
		try {
			fis = new FileInputStream(path);
			xssfWorkbook = new XSSFWorkbook(fis);
			int num = xssfWorkbook.getNumberOfSheets();
			if (sheetNum >= 0 && sheetNum <= num) {
				xssfSheet = xssfWorkbook.getSheetAt(sheetNum);
			} else {
				return null;
			}
			if (xssfSheet != null) {
				while (true) {
					xssfRow = xssfSheet.getRow(startRowNum);
					if (xssfRow != null) {
						for (Cell c : xssfRow) {
							isMerge = isMergedRegion(xssfSheet, startRowNum,
									c.getColumnIndex());
							if (isMerge)
								break;
						}
						if (isMerge) {
							for (Cell _c : xssfRow) {
								boolean _isMerge = isMergedRegion(xssfSheet,
										startRowNum, _c.getColumnIndex());
								// 判断是否具有合并单元格
								if (_isMerge) {
									String rs = getMergedRegionValue(xssfSheet,
											startRowNum, _c.getColumnIndex());
									_cellValues.add(rs);
								} else {
									_cellValues.add(getCellValue(_c));
								}
							}
							for (Object _cellValue : _cellValues) {
								if (!_tempCellValues.contains(_cellValue)) {
									_tempCellValues.add(_cellValue.toString());
								}
							}
							List<Object> ls = new ArrayList<>();
							for (Object _tempCellValue : _tempCellValues) {
								ls.add(_tempCellValue.toString());
							}
							cellValues.add(ls);
						} else {
							List<Object> ls = new ArrayList<>();
							for (int cellNum = 0; cellNum < xssfRow
									.getLastCellNum(); cellNum++) {
								cellValue = xssfRow.getCell(cellNum);
								if (cellValue != null) {
									ls.add(cellValue.toString());
								} else {
									ls.add("");
								}
							}
							cellValues.add(ls);
						}
						startRowNum++;
					} else {
						break;
					}
				}
			}
			fis.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cellValues;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 *            行下标
	 * @param column
	 *            列下标
	 * @return
	 */
	private boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 获取合并单元格的值
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellValue(fCell);
				}
			}
		}
		return null;
	}

	/**
	 * 获取单元格的值
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellValue(Cell cell) {
		if (cell == null)
			return "";
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
			return cell.getCellFormula();
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		}
		return "";
	}

}
